﻿using System;
using System.Collections.Generic;
using System.Text;
using ZLDC.Common.Estate;
using System.Data.SqlClient;
using System.Data;
using ZLDC.Topo.Model.Estate;

namespace ZLDC.DAL.Estate
{
    public class RoomNoDAL
    {
                                                                                                        public static int AddRoomNos(string name, string oname, int floor, int bid, string bname, string mark, string type, string id, string usage, string cUname, string cDept, string cDeptid, string status, string namepy, string othernamepy, int isvalid)
        {
            int rows = 0;

            using (_Database db = new _Database("estate"))
            {
                if (type == "Add")
                {
                    string sql = "insert into t_roomno(name,otherName,floor,bid,bname,remark,usage,bstatus,namepy,othernamepy,isvalid,registername,registerid,registerdept,registerdeptid,registerdate) values('" + name + "','" + oname + "'," + floor + "," + bid + ",'" + bname + "','" + mark + "','" + usage + "','" + status + "','" + name + "','" + othernamepy + "'," + isvalid + ",'" + _Utility.GetInfoBySession().m_sUserName + "'," + _Utility.GetInfoBySession().m_sUserID + ",'" + _Utility.GetInfoBySession().m_sDeptName + "'," + _Utility.GetInfoBySession().m_sDeptID + ",getDate())";
                    rows = db.ExecuteSql(sql);
                }
                else if (type == "Update")
                {
                    string sql = "update t_roomno set name='" + name + "',otherName='" + oname + "',namepy='" + namepy + "',othernamepy='" + othernamepy + "',floor='" + floor + "',bid='" + bid + "',bname='" + bname + "',remark='" + mark + "',usage='" + usage + "',registername='" + _Utility.GetInfoBySession().m_sUserName + "',registerid='" + _Utility.GetInfoBySession().m_sUserID + "',registerdept='" + _Utility.GetInfoBySession().m_sDeptName + "',registerdeptid='" + _Utility.GetInfoBySession().m_sDeptID + "',registerdate=getDate() where id='" + id + "'";
                    rows = db.ExecuteSql(sql);
                }
                else if (type == "SqAdd")
                {
                    string sql = "insert into t_roomno(name,otherName,floor,bid,bname,remark,isvalid,usage,bstatus,namepy,othernamepy,registername,registerid,registerdept,registerdeptid,registerdate) values('" + name + "','" + oname + "'," + floor + "," + bid + ",'" + bname + "','" + mark + "',1,'" + usage + "','" + status + "','" + namepy + "','" + othernamepy + "','" + _Utility.GetInfoBySession().m_sUserName + "'," + _Utility.GetInfoBySession().m_sUserID + ",'" + _Utility.GetInfoBySession().m_sDeptName + "'," + _Utility.GetInfoBySession().m_sDeptID + ",getDate())";
                    sql += ";insert into t_estate_other_name(type,tableName,tableId,modifyName,commitName,commitDept,commitDeptid,commitDate) values(3,'t_roomno',@@IDENTITY,'" + oname + "','" + cUname + "','" + cDept + "','" + cDeptid + "',GETDATE())";
                    rows = db.ExecuteSql(sql);
                }
            }

            return rows;
        }

                public static DataTable SelectFLoorByBid(int bid)
        {
            DataSet ds = null;

            using (_Database db = new _Database("estate"))
            {
                string sql = "select floor,usage from  t_roomno where bid=" + bid + " and delFlag=0 group by floor,usage ";
                ds = db.Query(sql);
            }

            DataTable dt = ds.Tables[0];
            return dt;
        }

        public static DataTable SelectFLoorByBid(int bid, string floor)
        {
            DataSet ds = null;

            using (_Database db = new _Database("estate"))
            {
                string sql = "select floor,usage from  t_roomno where bid=" + bid + " and delFlag=0  and floor='" + floor + "' group by floor,usage  ";
                ds = db.Query(sql);
            }

            DataTable dt = ds.Tables[0];
            return dt;
        }

                public static DataSet SelectRoomsById(string id)
        {
            DataSet ds = null;

            using (_Database db = new _Database("estate"))
            {
                string sql = "select * from t_roomno where id='" + id + "' and delFlag=0";
                ds = db.Query(sql);
            }

            return ds;
        }

                public static int SelectMaxFloorByBid(int bid)
        {
            DataSet ds = null;

            int floor = 0;
            using (_Database db = new _Database("estate"))
            {
                string sql = "select count(*) as num,floor from t_roomno where bid=" + bid + " and delFlag=0 group by floor order by num desc ";
                ds = db.Query(sql);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    floor = Convert.ToInt32(ds.Tables[0].Rows[0]["floor"]);
                }
            }

            return floor;
        }

                public static DataTable SelectNameByFloor(string floor, int bid)
        {
            DataSet ds = null;

            using (_Database db = new _Database("estate"))
            {
                string sql = "select * from t_roomno where floor='" + floor + "' and bid=" + bid + " and isValid=0 and delFlag=0";
                ds = db.Query(sql);
            }

            DataTable dt = ds.Tables[0];
            return dt;
        }

                public static DataSet SelectNameByBid(int bid)
        {
            DataSet ds = null;

            using (_Database db = new _Database("estate"))
            {
                string name = string.Empty;

                string sql = "select * from t_build where id=" + bid + " and delFlag=0";
                ds = db.Query(sql);
            }
            return ds;
        }

                                                public static int DeleteNameById(int id)
        {
            int row = 0;

            using (_Database db = new _Database("estate"))
            {
                string sql = "update t_roomno set delFlag=1 where id=" + id + "";
                row = db.ExecuteSql(sql);
            }

            return row;
        }
                                                public static int DeleteNameByIdAndIsValid(int id)
        {
            int row = 0;

            using (_Database db = new _Database("estate"))
            {
                string sql = "update t_roomno set delFlag=1 where id=" + id + " and isvalid>0 ";
                row = db.ExecuteSql(sql);
            }

            return row;
        }

                                                                                                public static int UpdateOtherName(string id, string oname, string type, string tablename, string commitName, string commitDept, string commitDeptID)
        {
            int row = 0;

            using (_Database db = new _Database("estate"))
            {
                string sql = "";

                if (type == "")
                    sql = "update t_roomno set otherName='" + oname + "' where id=" + id + "";
                else if (type != "")
                    sql = "insert into t_estate_other_name(type,tableName,tableId,modifyName,commitName,commitDept,commitDeptid,commitDate) values(" + type + ",'" + tablename + "'," + id + ",'" + oname + "','" + commitName + "','" + commitDept + "','" + commitDeptID + "',GETDATE())";

                row = db.ExecuteSql(sql);
            }

            return row;

        }

        public static DataTable p_roomno_list_table(int page, int pagesize, int total, int typeid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@total", total));
            parms.Add(new SqlParameter("@typeid", typeid));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_roomno_list", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                public static int SelectRooms(string bid, string floor, string name)
        {
            int rows = 0;
            using (_Database db = new _Database("estate"))
            {
                string sql = "select COUNT(*) as num from t_roomno  where bid='" + bid + "' and floor='" + floor + "' and name='" + name + "' and delFlag=0 ";
                DataSet ds = db.Query(sql);
                if(ds.Tables[0].Rows.Count>0)
                    rows =_Convert.ToInt(ds.Tables[0].Rows[0]["num"].ToString(),0); 

                db.close();
            }

            return rows;
        }

        public static DataSet SelectRoomsJson(string bid, string floor, string name)
        {
            DataSet ds = null;

            using (_Database db = new _Database("estate"))
            {
                string sql = "select id,isValid from t_roomno  where bid='" + bid + "' and floor='" + floor + "' and name='" + name + "' and delFlag=0 ";
                ds = db.Query(sql);

            }

            return ds;
        }

                                                                public static int UpdateStatusOrUsageByID(string usage, string bstatus, int id)
        {
            int row = 0;

            using (_Database db = new _Database("estate"))
            {
                string sql = "update t_roomno set bstatus='" + bstatus + "'  where id=" + id + "";
                row = db.ExecuteSql(sql);
            }

            return row;
        }

                                                                                public static DataTable p_roomno_list_condition_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_roomno_list_condition", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                public static int RoomnoCheck(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_roomno set isValid = 0 where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }
                public static DataTable ls_roomnoList(string  str,string roomnoid)
        {
            DataSet ds = null;

            using (_Database db = new _Database("estate"))
            {
                string sql = "";
                if (!string.IsNullOrEmpty(roomnoid) && roomnoid!="0")
                {
                    sql = " update t_roomno set delFlag=1 where id=" + roomnoid + ";";
                }
                sql += " select id,bname,floor,name,isvalid from   v_roomno " + str + " and delFlag=0";
                ds = db.Query(sql);
            }

            DataTable dt = ds.Tables[0];
            return dt;
        }
                public static DataTable ls_roomnoList_ss( string roomnoid,int eid,string type)
        {
            DataSet ds = null;
            string[] listid = roomnoid.Split('|');
            string sqlstr = "";
            if (type == "1")
            {
                if (listid.Length > 1)
                {
                    sqlstr += "update t_roomno set isValid=0 where id in(";
                    for (int i = 0; i < listid.Length; i++)
                    {
                        sqlstr += listid[i] + ",";
                    }
                    sqlstr = sqlstr.Substring(0, sqlstr.Length - 2);
                    sqlstr += ")";
                }
                else
                {
                    sqlstr += "update t_roomno set isValid=0 where id in(";
                    for (int i = 0; i < listid.Length; i++)
                    {
                        sqlstr += listid[i] + ",";
                    }
                    sqlstr = sqlstr.Substring(0, sqlstr.Length - 1);
                    sqlstr += ")";
                }
            }
            if (type == "2")
            {
                if (listid.Length > 1)
                {
                    sqlstr += "update t_roomno set delFlag=1 where id in(";
                    for (int i = 0; i < listid.Length; i++)
                    {
                        sqlstr += listid[i] + ",";
                    }
                    sqlstr = sqlstr.Substring(0, sqlstr.Length - 2);
                    sqlstr += ");";
                }
                else
                {
                    sqlstr += "update t_roomno set delFlag=1 where id in(";
                    for (int i = 0; i < listid.Length; i++)
                    {
                        sqlstr += listid[i] + ",";
                    }
                    sqlstr = sqlstr.Substring(0, sqlstr.Length - 1);
                    sqlstr += ");";
                }
            }
                using (_Database db = new _Database("estate"))
                {
                    string sql = sqlstr;

                    sql += " select  id,bname,floor,name,isvalid from  v_roomno  where delFlag=0 and eid=" + eid+" order by bname asc";
                    ds = db.Query(sql);
                }

            DataTable dt = ds.Tables[0];
            return dt;
        }

                public static DataSet GetDatable(string roomnoid)
        {
            DataSet ds = new DataSet();
            string sql = "select * from t_roomno where delFlag=0 and id=" + roomnoid;
            _Database db = new _Database("estate");
            ds = db.Query(sql);
            db.close();
            return ds;
        }
        public static int RoomnoCheck(int id, int isvalid, string Approval_views, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_roomno set isValid = " + isvalid + ",Approvalviews='" + Approval_views + "' where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }
                public  void UpdateRoomnoname(string name,string roomnoid)
        {
            string sql = "update t_roomno set name='" + name + "' where id=" + roomnoid;
            _Database db = new _Database("estate");
            db.ExecuteSql(sql);
            db.close();
            
        }
                                                                                public static DataTable p_v_roomno_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_v_roomno_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                public static int p_getHousingIDByRoomID(int roomid, int tradetype, out string outmsg, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@roomid", roomid));
            parms.Add(new SqlParameter("@tradetype", tradetype));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            db.execute_procedure("p_getHousingIDByRoomID", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return retval;
        }


                                                        public static int SelectRoomNoCountByBid(int bid, _Database db)
        {
            int count = 0;
            string sql = "select count(*) from t_RoomNo where bid=" + bid + " and delFlag=0 and isvalid=0 ";

            DataSet ds = db.Query(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                count = _Convert.ToInt(ds.Tables[0].Rows[0][0].ToString());
            }

            return count;

        }
    }
}
